package com.cf.dao.impl;

import com.cf.dao.WorksDao;
import com.cf.dao.db.BaseDao;
import com.cf.dao.entity.*;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;


public class WorksDaoImpl extends BaseDao implements WorksDao {
// 查询所有作品
    @Override
    public List<Works> Findallworks() throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "SELECT * FROM works";
        try (Connection conn = getDataSource().getConnection()) {
            return qr.query(conn, sql, new BeanListHandler<>(Works.class));
        }
    }
// 查询所有分类
    @Override
    public List<Category> Findallcategory() throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "SELECT * FROM category";
        try (Connection conn = getDataSource().getConnection()) {
            return qr.query(conn, sql, new BeanListHandler<>(Category.class));
        }
    }
// 根据分类查询作品
    @Override
    public List<Works> Findallworksbycategory(int category) throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "SELECT * FROM works WHERE categoryid = ?";
        try (Connection conn = getDataSource().getConnection()) {
            return qr.query(conn, sql, new BeanListHandler<>(Works.class), category);
        }
    }
//获取达人榜的作品id
    @Override
    public List<Works> Findtopworksid() throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "SELECT worksid FROM top";
        try (Connection conn = getDataSource().getConnection()) {
            return qr.query(conn, sql, new BeanListHandler<>(Works.class));
        }
    }
// 根据作品id查询作品
    @Override
    public List<Works> Findtopworks(String worksid) throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "SELECT * FROM works WHERE worksid = ?";
        try (Connection conn = getDataSource().getConnection()) {
            return qr.query(conn, sql, new BeanListHandler<>(Works.class), worksid);
        }
    }
// 获取推荐作品
    @Override
    public List<Works> findTopWorksDetails() throws SQLException {
        // Step 1: 获取所有的 worksid
        List<Works> topWorksIds = Findtopworksid();
        // Step 2: 使用所有的 worksid 查询对应的 Works 数据
        List<Works> topWorksDetails = new ArrayList<>();
        for (Works works : topWorksIds) {
            String worksid = String.valueOf(works.getWorksid());  // 获取每个 worksid
            List<Works> worksDetails = Findtopworks(worksid);  // 使用 worksid 查找 Works 数据
            topWorksDetails.addAll(worksDetails);  // 将查询到的结果添加到 topWorksDetails 列表中
        }
        return topWorksDetails;  // 返回所有的 works 数据
    }
//查询所有作品id，评分
    @Override
    public List<Score> findWorksScoreid() throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "SELECT id,worksid,score FROM score";
        try (Connection conn = getDataSource().getConnection()) {
            return qr.query(conn, sql, new BeanListHandler<>(Score.class));
        }
    }
    @Override
    public List<Works> Findworksbyid(int id) throws SQLException {
       QueryRunner qr = new QueryRunner();
       String sql = "SELECT * FROM works WHERE worksid = ?";
       Connection conn = getDataSource().getConnection();
       return qr.query(conn, sql, new BeanListHandler<>(Works.class),id);
    }
    @Override
    public List<Score> Findscoreall() throws SQLException {
       QueryRunner qr = new QueryRunner();
        String sql ="SELECT w.worksid, AVG(s.score) AS score FROM works w JOIN score s ON w.worksid = s.worksid GROUP BY w.worksid";
       Connection conn = getDataSource().getConnection();
       return qr.query(conn, sql, new BeanListHandler<>(Score.class));
    }

    @Override
    public long Findattentionall(int worksid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT COUNT(*) FROM attention WHERE worksid = ?";
        Connection conn = getDataSource().getConnection();
        return qr.query(conn, sql, new ScalarHandler<Long>(), worksid);
    }

    @Override
    public long Findattentionuser(int worksid, int userid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT COUNT(*) FROM attention WHERE worksid = ? AND userid = ?";
        Connection conn = getDataSource().getConnection();
        return qr.query(conn, sql, new ScalarHandler<Long>(), worksid, userid);
    }

    @Override
    public int MOattention(int worksid, int userid, Date date) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String selectSql = "SELECT COUNT(*) FROM attention WHERE worksid = ? AND userid = ?";
        Connection conn = getDataSource().getConnection();

        // 查询是否存在关注记录
        Long count = qr.query(conn, selectSql, new ScalarHandler<Long>(), worksid, userid);
        if (count != null && count > 0) {
            // 如果记录存在，则删除（不涉及 status）
            String deleteSql = "DELETE FROM attention WHERE worksid = ? AND userid = ?";
            return qr.update(conn, deleteSql, worksid, userid);  // 只传递 worksid 和 userid
        } else {
            // 如果记录不存在，插入新的关注记录
            String insertSql = "INSERT INTO attention (worksid, userid, date) VALUES (?, ?, ?)";
            return qr.update(conn, insertSql, worksid, userid, date);  // 插入数据
        }
    }

    @Override
    public int addBrowse(int id) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE works SET viewcount = viewcount + 1 WHERE id = ?";
        Connection conn = getDataSource().getConnection();
        return qr.update(conn, sql, id);
    }

    @Override
    public long findScoreById(int worksid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT AVG(s.score) AS score " +
                "FROM works w " +
                "JOIN score s ON w.id = s.worksid " +
                "WHERE w.id = ? " +
                "GROUP BY w.id";
        // 获取数据库连接
        Connection conn = getDataSource().getConnection();
        try {
            // 执行查询并返回结果
            Double result = qr.query(conn, sql, new ScalarHandler<>(), worksid);
            // 如果返回的结果为 null，表示没有找到数据，返回 0
            return result != null ? result.longValue() : 0L;
        } catch (SQLException e) {
            // 捕获并打印异常，方便调试
            e.printStackTrace();
            throw e; // 将异常重新抛出
        } finally {
            // 关闭连接（最好使用连接池管理连接）
            if (conn != null) {
                conn.close();
            }
        }
    }

    @Override
    public List<Works> findAttentionWorks(int userid) throws SQLException {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT \n" +
                "    w.*  \n" +
                "FROM \n" +
                "    attention a\n" +
                "JOIN \n" +
                "    works w ON a.worksid = w.id\n" +
                "WHERE \n" +
                "    a.userid = ?";  // 使用占位符 ? 来动态传入 userid

        try (Connection conn = getDataSource().getConnection()) {
            return qr.query(conn, sql, new BeanListHandler<>(Works.class), userid);  // 传递动态参数
        }
    }
    // 新的批量查询方法：查询多个 worksid 对应的作品
    private List<Works> findWorksByMultipleIds(String worksIds) throws SQLException {
        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "SELECT * FROM works WHERE worksid IN (" + worksIds + ")";
        try (Connection conn = getDataSource().getConnection()) {
            return qr.query(conn, sql, new BeanListHandler<>(Works.class));
        }
    }
}
